package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Employee;
import cn.gson.crm.vo.EmployeeVo;

public class EmpDao {

	private int pageSize = 7;

	// 根据员工名称获取员工信息
	public Employee getEmpByEmpName(String empName) throws SQLException {
		String sql = "select * from employee where emp_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, empName);
		Employee emp = null;
		if (data != null) {
			emp = builderEmp(data);
		}
		return emp;
	}

	/**
	 * 获取全部的在职员工信息
	 * 
	 * @param data
	 * @return
	 */
	public List<Employee> getEmpList() throws SQLException {
		// String sql = "SELECT emp_id,emp_name,emp_gender,emp_telphone,"
		// + "emp_email,emp_address,post_name AS post_id,dept_name "
		// + "AS dept_id,emp_status FROM employee INNER JOIN position"
		// + " ON position.post_id=employee.post_id INNER JOIN dept "
		// + "ON employee.dept_id=dept.dept_id WHERE emp_status=1";
		String sql = "SELECT * FROM employee where emp_status=1";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql);
		List<Employee> empList = new ArrayList<>();
		Employee emp = null;
		for (Map<String, Object> map : data) {
			emp = builderEmp(map);
			empList.add(emp);
		}

		return empList;
	}

	/**
	 * 删除员工
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */

	public boolean EmpDelete(int empstatus ,String id) throws SQLException {
		String sql = "UPDATE employee SET emp_status=?  WHERE emp_id=?";
		boolean result = Jdbc.getInstace().executeUpdate(sql,empstatus,id);
		return result;

	}

	/**
	 * 根据id查询员工
	 * 
	 * @param uid
	 * @return
	 * @throws SQLException
	 */
	public Employee getUserById(String uid) throws SQLException {
		String sql = "select * from employee where emp_id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, uid);
		Employee employee = null;
		if (data != null) {
			employee = builderEmp(data);
		}
		return employee;
	}

	/**
	 * 新增员工
	 * 
	 * @return
	 * @throws SQLException
	 */
	public boolean saveEmp(Employee emp) throws SQLException {
		String sql = "insert into employee(emp_name,emp_password,emp_gender,emp_telphone,emp_email,emp_address,post_id,dept_id,emp_status) VALUES(?,?,?,?,?,?,?,?,?);";
		boolean result = Jdbc.getInstace().executeUpdate(sql, emp.getEmpName(), emp.getEmpPassword(),
				emp.getEmpGender(), emp.getEmpTelphone(), emp.getEmpEmail(), emp.getEmpAddress(), emp.getPostId(),
				emp.getDeptId(), 1);
		return result;
	}

	/**
	 * 根据id更新员工信息
	 * 
	 * @param emp
	 * @return
	 * @throws SQLException
	 */
	public boolean updateEmp(Employee emp, String id) throws SQLException {
		String sql = "UPDATE employee SET emp_name=?,emp_password=?,emp_gender=?,emp_telphone=?,emp_email=?,emp_address=?,post_id=?,dept_id=? WHERE emp_id=?";
		boolean result = Jdbc.getInstace().executeUpdate(sql, emp.getEmpName(), emp.getEmpPassword(),
				emp.getEmpGender(), emp.getEmpTelphone(), emp.getEmpEmail(), emp.getEmpAddress(), emp.getPostId(),
				emp.getDeptId(), id);
		return result;
	}
	
	public List<Employee> getEmpListByDeptId(String id) throws SQLException {
		String sql="SELECT *FROM employee  WHERE dept_id = ?";
		List<Employee> empList=new ArrayList<>();
		Employee emp=null;
		List<Map<String, Object>> data=Jdbc.getInstace().executeQueryForList(sql, id);
		for (Map<String, Object> map : data) {
			emp=builderEmp(map);
			empList.add(emp);
		}
		return empList;
		
	}

	/**
	 * 获得employee、dept、position联合表信息
	 * 
	 * @return
	 * @throws SQLException
	 */

	public List<EmployeeVo> getEmpAllList(int page) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT employee.*, post_name,dept_name FROM employee INNER JOIN position ON position.post_id=employee.post_id INNER JOIN dept ON employee.dept_id=dept.dept_id WHERE emp_status=1  ORDER by emp_id DESC limit ?,?";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, start, pageSize);
		List<EmployeeVo> empVoList = new ArrayList<>();
		EmployeeVo empVo = null;
		for (Map<String, Object> map : data) {
			empVo = new EmployeeVo();
			empVo.setDeptId((Integer) map.get("dept_id"));
			empVo.setPostId((Integer) map.get("post_id"));
			empVo.setEmpId((Integer) map.get("emp_id"));
			empVo.setEmpName((String) map.get("emp_name"));
			empVo.setEmpGender((String) map.get("emp_gender"));
			empVo.setEmpTelphone((String) map.get("emp_telphone"));
			empVo.setEmpEmail((String) map.get("emp_email"));
			empVo.setEmpAddress((String) map.get("emp_address"));
			empVo.setPostName((String) map.get("post_name"));
			empVo.setDeptName((String) map.get("dept_name"));
			empVo.setEmpStatus(map.get("emp_status") + "");
			empVoList.add(empVo);
		}
		return empVoList;

	}

	// .......................................
	/**
	 * 带条件的分页查询
	 * 
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<EmployeeVo> getEmpAllList(int type, int page, String userName, String dept, String post)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT employee.*, post_name,dept_name FROM employee INNER JOIN position ON position.post_id=employee.post_id INNER JOIN dept ON employee.dept_id=dept.dept_id WHERE emp_status=? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, type, sql, userName, dept, post);
		sql += " order by emp_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		
		return queryUserList(sql, params.toArray());
	}

	/**
	 * 搜索总页数
	 * 
	 * @param type
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(int type, String userName, String dept, String post) throws SQLException {
		String sql = "select count(*) total from employee where emp_status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, type, sql, userName, dept, post);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	
	/**
	 * 获取所有职务是总管或主管或经理的人同时在职的
	 * @return
	 * @throws SQLException 
	 */
	public List<Employee> getAllMananger() throws SQLException{
		String sql = "select * from employee e inner join position p on e.post_id = p.post_id where e.post_id in ( " +
						"select post_id from position where post_name = '总管' and post_status = 1 or  post_name = '主管' and post_status = 1) and emp_status = 1";
		List<Employee> empList=new ArrayList<>();
		Employee emp=null;
		List<Map<String, Object>> data=Jdbc.getInstace().executeQueryForList(sql);
		for (Map<String, Object> map : data) {
			emp=builderEmp(map);
			empList.add(emp);
		}
		return empList;
	}

	private Employee builderEmp(Map<String, Object> data) {
		Employee emp = new Employee();
		emp.setEmpId(Integer.parseInt(data.get("emp_id").toString()));
		emp.setEmpName((String) data.get("emp_name"));
		emp.setEmpPassword((String) data.get("emp_password"));
		emp.setEmpGender((String) data.get("emp_gender"));
		emp.setEmpAddress((String) data.get("emp_address"));
		emp.setEmpEmail((String) data.get("emp_email"));
		emp.setEmpTelphone((String) data.get("emp_telphone"));
		emp.setDeptId(Integer.parseInt(data.get("dept_id").toString()));
		emp.setPostId(Integer.parseInt(data.get("post_id").toString()));
		emp.setEmpStatus(Integer.parseInt(data.get("emp_status").toString()));
		return emp;
	}

	private EmployeeVo builderEmpVo(Map<String, Object> map) {
		EmployeeVo empVo = new EmployeeVo();
		empVo.setDeptId((Integer) map.get("dept_id"));
		empVo.setPostId((Integer) map.get("post_id"));
		empVo.setEmpId((Integer) map.get("emp_id"));
		empVo.setEmpName((String) map.get("emp_name"));
		empVo.setEmpGender((String) map.get("emp_gender"));
		empVo.setEmpTelphone((String) map.get("emp_telphone"));
		empVo.setEmpEmail((String) map.get("emp_email"));
		empVo.setEmpAddress((String) map.get("emp_address"));
		empVo.setPostName((String) map.get("post_name"));
		empVo.setDeptName((String) map.get("dept_name"));
		empVo.setEmpStatus(map.get("emp_status") + "");
		return empVo;
	}

	// ………………………………………………
	private String buildSqlAndParams(List<Object> params, int type, String sql, String userName, String dept,
			String post) {
		params.add(type);
		if (!StringUtils.isNullOrEmpty(userName)) {
			sql += " and emp_name like ? ";
			params.add("%" + userName + "%");
		}

		if (!StringUtils.isNullOrEmpty(dept)) {
			sql += " and employee.dept_id = ? ";
			params.add(dept);
		}

		if (!StringUtils.isNullOrEmpty(post)) {
			sql += " and employee.post_id = ? ";
			params.add(post);
		}
		return sql;
	}

	private List<EmployeeVo> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<EmployeeVo> empList = null;
		if (listData != null && listData.size() > 0) {
			empList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				empList.add(builderEmpVo(data));
			}
		}
		return empList;
	}

}
